package com.dream.utils;

import com.alibaba.druid.pool.DruidDataSource;

import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;

public class DBUtil {

    private static Properties voConfigProperties;
    private static DruidDataSource dataSource;

    static {
        Properties p = new Properties();
        try {
            p.load(DBUtil.class.getClassLoader().getResourceAsStream("DBConfig.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        String driverName = p.getProperty("driverName");
        String url = p.getProperty("url");
        String username = p.getProperty("username");
        String password = p.getProperty("password");

        //创建德鲁伊数据库连接池
        dataSource = new DruidDataSource();

        //设置参数
        dataSource.setDriverClassName(driverName);//加载驱动
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setMaxActive(1000);

        voConfigProperties = new Properties();
        try {
            voConfigProperties.load(DBUtil.class.getClassLoader().getResourceAsStream("VOConfig.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //开启事务
    public static void startTransaction(){
        try {
            //获取连接对象
            Connection connection = getConnection();
            //开启事务
            connection.setAutoCommit(false);
            //存入ThreadLocal中
            local.set(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //提交事务
    public static void commit(){
        Connection connection = local.get();
        if(connection != null){
            try {
                connection.commit();
                connection.close();
                local.set(null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    //回滚事务
    public static void rollback(){
        Connection connection = local.get();
        if(connection != null){
            try {
                connection.rollback();
                connection.close();
                local.set(null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static ThreadLocal<Connection> local = new ThreadLocal<>();

    //获取连接对象
    public static Connection getConnection() throws SQLException{
        Connection connection = local.get();
        if(connection == null){
            connection = dataSource.getConnection();
            //设置事务的隔离级别
            connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
            local.set(connection);
        }
        return connection;
    }

    public static void close(Connection connection, Statement statement, ResultSet resultSet){
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null){
            try {
                if(connection.getAutoCommit()){//没有开启事务才能关闭
                    try {
                        System.out.println("关闭链接");
                        connection.close();
                        local.set(null);
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 查询
     * @param c 实体类的字节码文件对象
     * @param sql   SQL指令
     * @param param SQL参数
     * @param <T>  实体类的类型
     * @return  实体类对象的集合
     */
    public static<T> ArrayList<T>  commonQuery(Class<T> c,String sql, Object... param){

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            statement = connection.prepareStatement(sql);
            //设置SQL参数
            for (int i = 0; i < param.length; i++) {
                statement.setObject(i+1,param[i]);
            }
            //发送SQL指令
            resultSet = statement.executeQuery();

            //获取到表单对象
            ResultSetMetaData metaData = resultSet.getMetaData();
            //获取字段名的个数
            int columnCount = metaData.getColumnCount();

            ArrayList<T> list = new ArrayList<>();

            //遍历数据
            while(resultSet.next()){

                //利用反射创建对象
                T t = c.newInstance();

                for (int i = 1; i <= columnCount; i++) {
                    //获取字段名
                    String columnName = metaData.getColumnName(i);
                    //获取该字段名对应的数据
                    Object obj = resultSet.getObject(columnName);

                    //将数据存入对象中
                    try {//先找子类，再找父类
                        Field field = getField(c, columnName);
                        setField(field,t,obj);
                    } catch (NoSuchFieldException e) {//找配置文件中的属性名

                        System.out.println(columnName);

                        String voConfigFeildName = voConfigProperties.getProperty(columnName);
                        try {
                            Field field = getField(c, voConfigFeildName);
                            setField(field,t,obj);
                        } catch (NoSuchFieldException e1) {
                            e1.printStackTrace();
                        }
                    }
                }
                list.add(t);
            }
            return  list;
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        }
        return null;
    }

    //设置属性
    public static<T> void setField(Field field,T t,Object obj){
        field.setAccessible(true);
        try {
            field.set(t,obj);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
    }

    //获取到属性对象
    public static<T> Field getField(Class<T> c,String fieldName) throws NoSuchFieldException {
        try {
            Field field = c.getDeclaredField(fieldName);
            return  field;
        } catch (NoSuchFieldException e) {
            //找父类中的属性
            for( Class<? super T> superclass = c.getSuperclass();superclass!=null;superclass=superclass.getSuperclass()){
                try {
                    Field field = superclass.getDeclaredField(fieldName);
                    return field;
                } catch (NoSuchFieldException e1){
                }
            }
        }
        throw new NoSuchFieldException();
    }

    /**
     * 添加、修改、删除
     * @param sql   SQL指令
     * @param param 参数
     */
    public static void commonUpdate(String sql,Object... param){

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            statement = connection.prepareStatement(sql);
            for (int i = 0; i < param.length; i++) {
                statement.setObject(i+1,param[i]);
            }
            statement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
    }

    /**
     * 添加数据 -- 主键回填
     * @param sql   SQL指令
     * @param param 插入参数
     * @return  返回主键
     */
    public static int commonInsert(String sql,Object... param){

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            statement = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < param.length; i++) {
                statement.setObject(i+1,param[i]);
            }
            statement.executeUpdate();
            //获取主键
            resultSet = statement.getGeneratedKeys();
            if(resultSet.next()){
                int generatedKeys = resultSet.getInt(1);
                return generatedKeys;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return -1;
    }

    public static long queryCount(String sql){
        Connection connection=null;
        PreparedStatement ps=null;
        ResultSet resultSet=null;
        long count=0;
        try {
            connection = DBUtil.getConnection();
            ps = connection.prepareStatement(sql);
            resultSet = ps.executeQuery();
            if(resultSet.next()){
                count=resultSet.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,ps,resultSet);
        }
        return count;
    }

}
